package com.kehutong.charge.service.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.kehutong.charge.entity.*;
import com.kehutong.common.enums.UserType;
import com.kehutong.common.errcode.Assert;
import org.coraframework.bean.Beans;
import org.coraframework.converter.util.StringUtil;
import org.coraframework.inject.Inject;
import org.coraframework.json.JSONArray;
import org.coraframework.json.JSONObject;
import org.coraframework.logger.Logger;
import org.coraframework.logger.LoggerFactory;
import org.coraframework.orm.Pool;
import org.coraframework.orm.jdbc.JdbcSession;
import org.coraframework.orm.jdbc.Page;
import org.coraframework.orm.jdbc.connection.ThreadLocalCache;
import org.coraframework.orm.jdbc.execute.Condition;
import org.coraframework.orm.jdbc.execute.PageCondition;
import org.coraframework.orm.jdbc.execute.QueryCondition;
import org.coraframework.orm.jdbc.execute.entity.FindArray;
import org.coraframework.orm.jdbc.execute.entity.FindPage;
import org.coraframework.util.Objects;
import org.coraframework.util.time.TimeUtil;
import com.kehutong.charge.dao.ChargeBillItemDao;
import com.kehutong.charge.dto.BillItemsImportDTO;
import com.kehutong.charge.dto.ChargeBillItemAppDTO;
import com.kehutong.charge.service.CategoryBillService;
import com.kehutong.charge.service.ChargeBillItemsService;
import com.kehutong.charge.service.CommonService;
import com.kehutong.charge.util.CommonClient;
import com.kehutong.charge.util.CommonUtil;
import com.kehutong.charge.util.ConvertUtil;
import com.kehutong.charge.util.enums.ChargeBillStatusEnum;
import com.kehutong.charge.util.enums.ChargeLogEnum;
import com.kehutong.charge.vo.ItemsBillTemplateExcelVO;
import com.kehutong.common.DailyUtil;
import com.kehutong.common.DeptService;
import com.kehutong.common.DoveClient;
import com.kehutong.common.Java8TimeUtils;
import com.kehutong.common.constant.RegexConstant;
import com.kehutong.common.dto.DeptDTO;
import com.kehutong.common.dto.UniformSendReqDto;
import com.kehutong.common.enums.MessageBusinessEnum;
import com.kehutong.common.enums.TemplateType;
import com.kehutong.common.errcode.Result;
import com.kehutong.common.excel.entity.ImportResult;
import com.kehutong.common.excel.tool.ExcelTool;
import com.kehutong.common.urlconfig.WebUrlConfig;
import com.kehutong.common.util.BigDecimalUtils;
import com.kehutong.common.util.ConvertUtils;
import com.kehutong.common.util.ListUtils;
import com.kehutong.common.util.TemplateReplaceUtil;
import com.kehutong.common.util.Token;

/**
 * @program: kht
 * @description: ChargeBillItemsServiceImpl
 * @author: joneron
 * @create: 2021/7/5
 **/
public class ChargeBillItemsServiceImpl implements ChargeBillItemsService {

    private static final Logger logger = LoggerFactory.getLogger(ChargeBillItemsServiceImpl.class);
    private static List<ItemsBillTemplateExcelVO> template = new ArrayList<>();
    static {
        ItemsBillTemplateExcelVO vo = new ItemsBillTemplateExcelVO();
        vo.setCommunityName("七星小区（示例）");
        vo.setBuildName("1栋");
        vo.setUnitName("1单元");
        vo.setHouseNumber("1001");
        vo.setBillYearMonth("2021-05");
        vo.setFeeTypeName("水费");
        vo.setChargeName("张三");
        vo.setMobile("13244354567");
        vo.setBillAmount("400.00");
        vo.setBillCycleStart("2021-05-01");
        vo.setBillCycleEnd("2021-05-31");
        vo.setBillDate("2021-06-01");
        vo.setChargeDate("2021-06-15");
        vo.setPenaltyDate("2021-06-22");
        template.add(vo);
    }


    @Inject
    private JdbcSession jdbcSession;
    @Inject
    CommonClient commonClient;
    @Inject
    CommonService commonService;
    @Inject
    ExcelTool excelTool;
    @Inject
    DeptService deptService;
    @Inject
    ChargeBillItemDao chargeBillItemDao;
    @Inject
    private DoveClient doveClient;
    @Inject
    private WebUrlConfig webUrlConfig;

    @Inject
    private CategoryBillService categoryBillService;


    @Override
    public Page<ChargeBillItems> findPage(JSONObject jsonObject) {
        String id = jsonObject.getString("id");
        String billStatus = jsonObject.getString("billStatus");
        String chargeStatus = jsonObject.getString("chargeStatus");
        String billCycleStart = jsonObject.getString("billCycleStart");
        String billCycleEnd = jsonObject.getString("billCycleEnd");
        String feeTypeName = jsonObject.getString("feeTypeName");

        FindPage<ChargeBillItems> find = jdbcSession.findPage(ChargeBillItems.class);
        if(!Objects.isEmpty(id)){
            find.like("itemBillNo",jsonObject.getString("id"));
        }

        find.eq("deleted", false);
        find.eq(jsonObject, "meterTypeNo");
        find.eq(jsonObject, "communityNo");
        find.eq(jsonObject, "billYearMonth");
        find.eq(jsonObject, "itemBillNo");
        find.eq(jsonObject, "houseNo");
        if(!Objects.isEmpty(billStatus)){
            find.eq("billStatus", billStatus);
        }
        if(!Objects.isEmpty(chargeStatus)){
            find.eq("chargeStatus", chargeStatus);
        }
        if(!Objects.isEmpty(billCycleStart) && !Objects.isEmpty(billCycleEnd)){
            find.between("createTime", billCycleStart,billCycleEnd);
        }
        if(!Objects.isEmpty(feeTypeName)){
            find.eq("feeTypeName",feeTypeName);
        }
        find.order("createTime", "desc");

        return find.page(jsonObject).exe();
    }


    @Override
    public void pageExport(HttpServletRequest req, HttpServletResponse resp, JSONObject jsonObject) {
        jsonObject.put("pageNo", 1);
        jsonObject.put("pageSize", ExcelTool.MAX_ROWS);
        List<ChargeBillItems> list = findPage(jsonObject).getList();
        Assert.notEmpty(list,"导出失败！没有找到相关的类目账单数据.");
        List<ItemsBillTemplateExcelVO> dtoList = ListUtils.copyBeanList(list, ItemsBillTemplateExcelVO::new);

        List<Map<String,Object>> houseList = null;
        try {
            Set<String> ids = dtoList.stream().map(l -> l.getHouseNo()).collect(Collectors.toSet());
            JSONObject param = new JSONObject();
            param.put("ids",ids);
            houseList = commonClient.getHouseByIds(param);
        } catch (Exception e) {
            logger.error("抄表导出获取房屋信息错误",e);
            excelTool.excelDownload(req,resp,dtoList,"类目账单列表导出");
        }
        List<Map<String, Object>> finalHouseList = houseList;
        dtoList.stream().map(r-> finalHouseList.stream().filter(h->h.get("id").equals(r.getHouseNo())).findFirst().map(h->{
            r.setCommunityName(ConvertUtils.toNoNullString(h.get("communityName")));
            r.setBuildName(ConvertUtils.toNoNullString(h.get("buildName")));
            r.setUnitName(ConvertUtils.toNoNullString(h.get("unitName")));
            r.setHouseNumber(ConvertUtils.toNoNullString(h.get("mergeName")));
            return r;
        }).orElse(null)).filter(java.util.Objects::nonNull).collect(Collectors.toList());

        excelTool.excelDownload(req,resp,dtoList,"类目账单列表导出");
    }


    @Override
    public List<ChargeBillItems> findList(JSONObject jsonObject) {

        return jdbcSession.findArray(ChargeBillItems.class)
                .eq("deleted", false)
                .eq(jsonObject,"houseBillNo")
                .eq(jsonObject,"meterTypeNo")
                .eq(jsonObject,"billStatus")
                .eq(jsonObject,"userNo")
                .eq(jsonObject,"houseNo")
                .like(jsonObject,"billYearMonth")
                .exe();
    }


    /**
     * 类目账单生成
     */
    @Deprecated
    @Override
    public Object save(ChargeBillItems chargeBillItems) throws Exception {
        if (true) {
            return categoryBillService.save(chargeBillItems);
        }
        //1、根据小区查询数据
        String communityNo = chargeBillItems.getCommunityNo();
        //定义房屋集合
        List<String> houseList = new LinkedList<>();
        //楼栋集合
        List<String> buildNoList = chargeBillItems.getBuildNoList();
        if(!Objects.isEmpty(buildNoList)){
            for (int i = 0; i < buildNoList.size(); i++) {
                Map<String, JSONObject> objectMap = commonClient.getBuildMap(buildNoList.get(i));
                for (String house: objectMap.keySet()) {
                    houseList.add(house);
                }
            }
        }
        //房屋集合
        List<String> houseNoList = chargeBillItems.getHouseNoList();
        if(!Objects.isEmpty(houseNoList)){
            for (int i = 0; i < houseNoList.size(); i++) {
                houseList.add(houseNoList.get(i));
            }
        }
        //如果楼栋和房屋都没有选，则生成整个小区账单
        houseList.removeAll(Collections.singleton(null));
        if(Objects.isEmpty(houseList)){
            Map<String, JSONObject> objectMap = commonClient.getHouseMap(communityNo);
            for (String houseNo: objectMap.keySet()) {
                houseList.add(houseNo);
            }
        }
        //生成账单
        return generateItemBill(chargeBillItems,houseList);
    }

    /**
     * 根据房屋生成账单
     * @param chargeBillItems
     * @return
     */
    private Object generateItemBill(ChargeBillItems chargeBillItems,List<String> houseList) throws Exception {

        List<ChargeBillCommunity> communityList = jdbcSession.findArray(ChargeBillCommunity.class)
                .eq("deleted", false)
                .eq("communityNo",chargeBillItems.getCommunityNo())
                .eq("billYearMonth",chargeBillItems.getBillYearMonth())
                .eq("billStatus",ChargeBillStatusEnum.BILL_WAIT_PAID)
                .exe();

        // 小区信息存在，不等于作废的数据
        for (int i = 0; i < communityList.size(); i++) {
            ChargeBillCommunity cbc = communityList.get(i);
            cbc.setBillStatus(ChargeBillStatusEnum.BILL_PAID_CLOSE);
            cbc.setUpdateName(chargeBillItems.getCreateName());
            cbc.setUpdateTime(TimeUtil.timestamp());
            //3、小区数据作废
            jdbcSession.updateById(cbc);

        }

        //生成小区账单
        ChargeBillCommunity community = Pool.newInstance(ChargeBillCommunity.class);
        String communityBillNo = CommonUtil.generateRandom("XQ");
        community.setCommunityBillNo(communityBillNo);
        chargeBillItems.setCommunityBillNo(community.getCommunityBillNo());

        BigDecimal amountComm = BigDecimal.ZERO;
        int num = 0 ; //累计生成房屋条数
        for (int b = 0; b < houseList.size(); b++) {

            String houseNo = houseList.get(b);
            //判断房屋是否有账单
            List<ChargeBillItems> billItems = jdbcSession.findArray(ChargeBillItems.class)
                    .eq("deleted", false)
                    .eq("houseNo",houseNo)
                    .eq("billYearMonth",chargeBillItems.getBillYearMonth())
                    .eq("billStatus",ChargeBillStatusEnum.BILL_WAIT_PAID).exe();

            BigDecimal amountHouse = BigDecimal.ZERO;
            String houseBillNos = null;
            for (int i = 0; i < billItems.size(); i++) {
                ChargeBillItems billItems1 = billItems.get(i);
                houseBillNos = billItems1.getHouseBillNo();
                amountHouse = amountHouse.add(billItems1.getBillAmount());
                //先作废
                billItems1.setBillStatus(ChargeBillStatusEnum.BILL_PAID_CLOSE);
                if(billItems1.getMeterTypeNo().equals(chargeBillItems.getMeterTypeNo())){
                    jdbcSession.updateById(billItems1);
                }
            }

            //房屋作废
            if(null != houseBillNos){

                ChargeBillHouse houses = jdbcSession.findOne(ChargeBillHouse.class)
                        .eq("deleted", false)
                        .eq("houseBillNo",houseBillNos)
                        .eq("billStatus",ChargeBillStatusEnum.BILL_WAIT_PAID).exe();

                houses.setBillStatus(ChargeBillStatusEnum.BILL_PAID_CLOSE);
                jdbcSession.updateById(houses);

                commonService.insetLogger(houses.getHouseBillNo(),
                        ChargeLogEnum.LOG_CHARGE_BILL_COM.getDesc(),
                        "常规账单作废",
                        ChargeBillStatusEnum.BILL_PAID_CLOSE.getDesc(),
                        ChargeBillStatusEnum.BILL_UN_PAID.getDesc(), "操作成功", chargeBillItems.getCreateName());
            }


            //1、根据房屋id查询费用配置绑定项,可能配置多次
            ChargeHouseConfig houseConfig = jdbcSession.findOne(ChargeHouseConfig.class)
                    .eq("deleted", false)
                    .eq("houseNo",houseNo)
                    .exe();
            if(null == houseConfig){
                logger.warn("房屋：{},没有设置收费项目！",houseNo);
                continue;
            }

            //查询配置类型
            ChargeTypeConfig typeConfigs = jdbcSession.findOne(ChargeTypeConfig.class)
                    .eq("deleted", false)
                    .eq("meterTypeNo",chargeBillItems.getMeterTypeNo())
                    .exe();
            //查询费用配置项
            List<ChargeHouseSet> houseSetList = jdbcSession.findArray(ChargeHouseSet.class)
                    .eq("deleted", false)
                    .eq("chargeHouseNo",houseConfig.getId())
                    .eq("feeTypeNo",typeConfigs.getId())
                    .exe();

            if(null == houseSetList || 0 == houseSetList.size()){
                logger.warn("房屋：{},没有设置收费项目！",houseNo);
                continue;
            }

            //生成账单
            ChargeBillHouse chargeBillHouse = Pool.newInstance(ChargeBillHouse.class);
            chargeBillHouse.setHouseBillNo(CommonUtil.generateRandom("FW"));
            chargeBillHouse.setChargeName(houseConfig.getChargeName());
            chargeBillHouse.setResidentsType(houseConfig.getReType());
            chargeBillHouse.setBillYearMonth(chargeBillItems.getBillYearMonth());
            chargeBillHouse.setUserNo(houseConfig.getOwnerNo());
            chargeBillHouse.setHouseNo(houseNo);
            community.setCommunityName(houseConfig.getCommunityName());

            //查询该房屋的基本信息
            JSONObject jsonOb = commonClient.getHouseById(chargeBillItems.getRemark(),chargeBillHouse.getHouseNo());
            String communityName = jsonOb.getString("communityName");
            String buildName = jsonOb.getString("buildName");
            String buildNo = jsonOb.getString("buildNo");
            String unitName = jsonOb.getString("unitName");
            String mergeName = jsonOb.getString("mergeName");
            String address = communityName +"-"+ buildName + unitName +"-"+ mergeName;
            chargeBillHouse.setBillAddress(address);
            chargeBillHouse.setCommunityName(communityName);


            for (int i = 0; i < houseSetList.size(); i++) {
                //3、根据费用配置项id查询具体配置信息，并计算费用
                ChargeHouseSet chs = houseSetList.get(i);

                //4、查询费用配置参数
                ChargeProjectConfig pconfig = jdbcSession.findById(ChargeProjectConfig.class,chs.getProjectFeeNo());
                
                //5、查询仪表类型
                ChargeTypeConfig typeConfig = jdbcSession.findById(ChargeTypeConfig.class,pconfig.getFeeTypeNo());

                //6、根据房屋和类型、月份、周期、查询抄表使用量(有多次抄表的情况)
                List<MeterReading> meterReading = jdbcSession.findArray(MeterReading.class)
                        .eq("deleted", false)
                        .eq("houseNo",chs.getHouseNo())
                        .eq("meterTypeNo",chargeBillItems.getMeterTypeNo())
                        .like("endTime",chargeBillItems.getBillYearMonth())
                        .exe();

                if(null == meterReading || meterReading.size() == 0){
                    logger.warn("房间：{},在{}没有抄表数据！",chs.getHouseNo(),chargeBillItems.getBillYearMonth());
                    continue;
                }
                num += 1;
                for (int j = 0; j < meterReading.size(); j++) {

                    MeterReading reading = meterReading.get(j);
                    ChargeBillItems items = Pool.newInstance(ChargeBillItems.class);
                    String billNO = CommonUtil.generateRandom("LM");
                    items.setItemBillNo(billNO);
                    items.setBillStatus(ChargeBillStatusEnum.BILL_WAIT_PAID);
                    items.setChargeStatus(ChargeBillStatusEnum.BILL_UN_PAID);

                    //7、计算单个类型的总费用
                    BigDecimal amount = commonService.calcFeeCommon(pconfig,reading.getTimeUseNumber(),billNO);
                    amount = amount.setScale(2,BigDecimal.ROUND_UP); //保存两位小数,只入不舍
                    amountHouse = amountHouse.add(amount);
                    items.setBillAmount(amount);
                    items.setBuildNo(buildNo);
                    items.setMergeName(mergeName);
                    items.setBuildName(buildName);
                    items.setFeeTypeName(typeConfig.getFeeTypeName());
                    items.setFeeTypeNo(typeConfig.getId());
                    items.setChargeName(houseConfig.getChargeName());
                    items.setUserNo(houseConfig.getOwnerNo());
                    items.setResidentsType(houseConfig.getReType());
                    items.setCreateName(chargeBillItems.getCreateName());
                    items.setBillCycleStart(chargeBillItems.getBillCycleStart());
                    items.setBillCycleEnd(chargeBillItems.getBillCycleEnd());
                    items.setBillDate(chargeBillItems.getBillDate());
                    items.setChargeDate(chargeBillItems.getChargeDate());
                    items.setDepartmentNo(chargeBillItems.getDepartmentNo());
                    items.setDepartmentName(chargeBillItems.getDepartmentName());
                    items.setDepartmentNos(chargeBillItems.getDepartmentNos());
                    items.setCommunityNo(reading.getCommunityNo());
                    items.setCommunityName(reading.getCommunityName());
                    items.setCommunityBillNo(chargeBillItems.getCommunityBillNo());
                    items.setHouseNo(chs.getHouseNo());
                    items.setHouseBillNo(chargeBillHouse.getHouseBillNo());
                    items.setMeterTypeNo(reading.getMeterTypeNo());
                    items.setMeterTypeName(reading.getMeterTypeName());
                    items.setMeterClassifyName(reading.getMeterClassifyName());
                    items.setMeterClassifyNo(reading.getMeterClassifyNo());
                    items.setBillYearMonth(chargeBillHouse.getBillYearMonth());
                    items.setUsageNumber(reading.getTimeUseNumber());
                    items.setBillAddress(address);
                    items.setPenaltyDays(Java8TimeUtils.subDate(chargeBillItems.getPenaltyDate()));
                    items.setPenaltyAmount(new BigDecimal(0));
                    items.setPenaltyDate(chargeBillItems.getPenaltyDate());
                    items.setCalcUnit(reading.getCalcUnit());
                    items.setPayableAmount(amount);
                    items.setPrice(pconfig.getPrice());
                    items.setLastTimeNumber(reading.getLastTimeNumber());
                    items.setTimeNumber(reading.getTimeNumber());
                    items.setStartTime(reading.getStartTime());
                    items.setEndTime(reading.getEndTime());

                    //8、记录费用项
                    jdbcSession.insert(items);

                    commonService.insetLogger(items.getItemBillNo(),
                            ChargeLogEnum.LOG_CHARGE_BILL_CATE.getDesc(),
                            "创建类目账单",
                            ChargeBillStatusEnum.BILL_WAIT_PAID.getDesc(),
                            ChargeBillStatusEnum.BILL_UN_PAID.getDesc(), "操作成功", chargeBillItems.getCreateName());


                    //发送消息
                    /**
                     * 成功之后调用消息
                     */
                    // todo: 调用消息
                    try{
                        //根据用户查询手机号码
                        JSONObject object = commonClient.findResidentInfo(items.getUserNo());

                        //切换公司
                        String companyNo = ThreadLocalCache.current().getCorpId();
                        ChargeOrderManage manage = Pool.newInstance(ChargeOrderManage.class);
                        List<String> phoneList = new ArrayList<>();
                        phoneList.add(object.getString("mobile")); //手机号
                        msgUniformSend(items,items.getUserNo(), TemplateType.PAY_C_MONTH_BILL_DATE, phoneList,true, companyNo);
                    }catch (Exception e){}
                }
            }

            amountComm = amountComm.add(amountHouse);
            chargeBillHouse.setBillAmount(amountHouse);
            chargeBillHouse.setBillAmountUnPaid(amountHouse);

            //a大于b, 否则就是没有抄表信息
            if(amountHouse.compareTo(BigDecimal.ZERO) == 1){
                //9、添加房屋账单
                insetHouseBill(chargeBillItems,chargeBillHouse);
            }
        }

        //a大于b, 否则就是没有抄表信息
        if(amountComm.compareTo(BigDecimal.ZERO) == 1){
            community.setBillAmountTotal(amountComm);
            community.setBillTotal(num);
            community.setCommunityNo(chargeBillItems.getCommunityNo());
            community.setBillStatus(ChargeBillStatusEnum.BILL_WAIT_PAID);
            community.setCreateName(chargeBillItems.getCreateName());
            community.setBillCycleStart(chargeBillItems.getBillCycleStart());
            community.setBillCycleEnd(chargeBillItems.getBillCycleEnd());
            community.setBillDate(chargeBillItems.getBillDate());
            community.setChargeDate(chargeBillItems.getChargeDate());
            community.setBillYearMonth(chargeBillItems.getBillYearMonth());
            community.setPenaltyDate(chargeBillItems.getPenaltyDate());
            community.setDepartmentNo(chargeBillItems.getDepartmentNo());
            community.setDepartmentName(chargeBillItems.getDepartmentName());
            community.setDepartmentNos(chargeBillItems.getDepartmentNos());

            jdbcSession.insert(community);
        }else {
            return org.coraframework.mvc.http.result.Result.error(2020,"该小区没有设置收费项目或抄表数据！");
        }

        return Result.success();
    }

    /**
     * 添加房屋账单信息
     * @return
     */
    private Object insetHouseBill(ChargeBillItems chargeBillItems,ChargeBillHouse chargeBillHouse) throws Exception {

        chargeBillHouse.setCommunityNo(chargeBillItems.getCommunityNo());
        chargeBillHouse.setCommunityBillNo(chargeBillItems.getCommunityBillNo());
        chargeBillHouse.setBillStatus(ChargeBillStatusEnum.BILL_WAIT_PAID);
        chargeBillHouse.setChargeStatus(ChargeBillStatusEnum.BILL_UN_PAID);
        chargeBillHouse.setBillCycleStart(chargeBillItems.getBillCycleStart());
        chargeBillHouse.setBillCycleEnd(chargeBillItems.getBillCycleEnd());
        chargeBillHouse.setBillDate(chargeBillItems.getBillDate());
        chargeBillHouse.setChargeDate(chargeBillItems.getChargeDate());
        chargeBillHouse.setPenaltyDate(chargeBillItems.getPenaltyDate());
        //计算违约天数,生成账单没有违约
        chargeBillHouse.setPenaltyDays(Java8TimeUtils.subDate(chargeBillItems.getPenaltyDate()));
        chargeBillHouse.setPenaltyAmount(new BigDecimal(0));
        chargeBillHouse.setDepartmentNo(chargeBillItems.getDepartmentNo());
        chargeBillHouse.setDepartmentName(chargeBillItems.getDepartmentName());
        chargeBillHouse.setDepartmentNos(chargeBillItems.getDepartmentNos());
        chargeBillHouse.setCreateName(chargeBillItems.getCreateName());

        commonService.insetLogger(chargeBillHouse.getHouseBillNo(),
                ChargeLogEnum.LOG_CHARGE_BILL_COM.getDesc(),
                "创建常规账单",
                ChargeBillStatusEnum.BILL_WAIT_PAID.getDesc(),
                ChargeBillStatusEnum.BILL_UN_PAID.getDesc(), "操作成功", chargeBillItems.getCreateName());

        return jdbcSession.insert(chargeBillHouse);
    }

    @Override
    public Object update(ChargeBillItems chargeBillItems) throws Exception {
        return jdbcSession.updateById(chargeBillItems);
    }

    @Override
    public Object deleteById(String id) {

        ChargeBillItems result = Pool.get(ChargeBillItems.class, id);
        if (result != null) {
            result.setDeleted(true);
            jdbcSession.updateById(result);
        }
        return 1;
    }

    @Override
    public Object getObj(JSONObject jsonObject) {
        return jdbcSession.findArray(ChargeBillItems.class)
                .eq(jsonObject, "houseNo")
                .eq(jsonObject, "itemBillNo")
                .eq(jsonObject, "meterTypeNo")
                .eq(jsonObject, "billYearMonth")
                .eq(jsonObject, "chargeName")
                .noEq("billStatus", ChargeBillStatusEnum.BILL_PAID_CLOSE.getId())
                .order("createTime", "desc")
                .exe();
    }

    @Override
    public Object findBillLadderList(JSONObject jsonObject) {

        return jdbcSession.findArray(ChargeBillLadder.class)
                .eq("deleted", false)
                .eq(jsonObject,"itemBillNo")
                .eq(jsonObject,"houseNo")
                .eq(jsonObject,"meterTypeNo")
                .eq(jsonObject,"billYearMonth")
                .exe();
    }

    @Override
    public Object resetItemBill(JSONObject jsonObject, Token token) throws Exception {

        return null;
    }

    @Override
    public Object cancelItemBill(JSONObject jsonObject, Token token) {
        //所有类目账单编号
        JSONArray jsonArray = jsonObject.getJSONArray("itemBillNos");
        if(Objects.isEmpty(jsonArray)){
            return Result.error(0,"需要作废的账单id不能为空.");
        }
        List<ChargeBillItems> itemsList = jdbcSession.findArray(ChargeBillItems.class)
                .eq("deleted", false)
                .in("itemBillNo", jsonArray)
                .exe();

        for (int i = 0; i < itemsList.size(); i++) {
            ChargeBillItems items = itemsList.get(i);
            if(items.getChargeStatus().equals(ChargeBillStatusEnum.BILL_UN_PAID)){
                String houseBillNo = items.getHouseBillNo();
                items.setBillStatus(ChargeBillStatusEnum.BILL_PAID_CLOSE);
                items.setChargeStatus(ChargeBillStatusEnum.BILL_UN_PAID);
                items.setUpdateName(token.getUsername());
                items.setUpdateTime(TimeUtil.timestamp());
                items.setHouseBillNo(null);
                jdbcSession.updateById(items);

                // 更改房屋账单的金额和状态
                ChargeBillHouse chargeBillHouse = jdbcSession.findOne(ChargeBillHouse.class).eq("houseBillNo", houseBillNo).exe();
                List<ChargeBillItems> houseBillItems = jdbcSession.findArray(ChargeBillItems.class)
                        .eq("deleted", false)
                        .eq("houseBillNo", houseBillNo)
                        .exe();
                List<ChargeBillItems> finishBillItems = new ArrayList<>();
                List<ChargeBillItems> waitPaidBillItems = new ArrayList<>();
                for (ChargeBillItems each : houseBillItems) {
                    if (each.getBillStatus() == ChargeBillStatusEnum.BILL_PAID_FINISH) {
                        finishBillItems.add(each);
                    } else if (each.getBillStatus() == ChargeBillStatusEnum.BILL_WAIT_PAID) {
                        waitPaidBillItems.add(each);
                    }
                }

                // 若已完成和待付款账单为0
                if (finishBillItems.size() == 0 && waitPaidBillItems.size() == 0) {
                    chargeBillHouse.setBillStatus(ChargeBillStatusEnum.BILL_PAID_CLOSE);
                    chargeBillHouse.setChargeStatus(ChargeBillStatusEnum.BILL_UN_PAID);
                } else {
                    // 若部份已缴费，部份未缴费
                    if (finishBillItems.size() > 0 && waitPaidBillItems.size() > 0) {
                        chargeBillHouse.setBillStatus(ChargeBillStatusEnum.BILL_WAIT_PAID);
                        chargeBillHouse.setChargeStatus(ChargeBillStatusEnum.BILL_PAID_PART);
                        chargeBillHouse.setBillAmount(BigDecimalUtils.sub(chargeBillHouse.getBillAmount(), items.getBillAmount(), 2));
                        chargeBillHouse.setBillAmountUnPaid(BigDecimalUtils.sub(chargeBillHouse.getBillAmountUnPaid(), items.getBillAmount(), 2));
                    }
                    // 若全部缴费
                    else if (finishBillItems.size() > 0) {
                        chargeBillHouse.setBillStatus(ChargeBillStatusEnum.BILL_PAID_FINISH);
                        chargeBillHouse.setChargeStatus(ChargeBillStatusEnum.BILL_PAID);
                        chargeBillHouse.setBillAmount(BigDecimalUtils.sub(chargeBillHouse.getBillAmount(), items.getBillAmount(), 2));
                        chargeBillHouse.setBillAmountUnPaid(BigDecimal.ZERO);
                    }
                    // 若全部未缴费
                    else {
                        chargeBillHouse.setBillStatus(ChargeBillStatusEnum.BILL_WAIT_PAID);
                        chargeBillHouse.setChargeStatus(ChargeBillStatusEnum.BILL_UN_PAID);
                        chargeBillHouse.setBillAmount(BigDecimalUtils.sub(chargeBillHouse.getBillAmount(), items.getBillAmount(), 2));
                        chargeBillHouse.setBillAmountUnPaid(BigDecimalUtils.sub(chargeBillHouse.getBillAmountUnPaid(), items.getBillAmount(), 2));
                    }
                }
                jdbcSession.updateById(chargeBillHouse);

                // 更改抄表统计状态
                List<MeterReadStatist> readStatists = jdbcSession.findArray(MeterReadStatist.class)
                        .eq("deleted", false)
                        .eq("houseNo", items.getHouseNo())
                        .eq("meterTypeNo", items.getMeterTypeNo())
                        .eq("meterClassifyNo", items.getMeterClassifyNo())
                        .like("readYearMonth", items.getBillYearMonth())
                        .exe();
                if (!Objects.isEmpty(readStatists)) {
                    for (MeterReadStatist each : readStatists) {
                        each.setFeeStatus("2");
                    }
                    jdbcSession.updateById(readStatists);
                }
            }else{
                commonService.insetLogger(items.getItemBillNo(),
                        ChargeLogEnum.LOG_CHARGE_BILL_CATE.getDesc(),
                        "类目账单作废",
                        ChargeBillStatusEnum.BILL_PAID_CLOSE.getDesc(),
                        ChargeBillStatusEnum.BILL_UN_PAID.getDesc(), "操作失败(原因：订单不是未缴费状态)", token.getUsername());
                return Result.error(0,"未缴费状态才能作废");
            }
        }

        return Result.success("作废成功");
    }

    @Override
    public Object findBillManage(JSONObject jsonObject, Token token) throws Exception {

        PageCondition query = Condition.createPage(false);

        String meterMonth = jsonObject.getString("readYearMonth");
        if(!StringUtil.isEmpty(meterMonth)){
            //meterMonth = DailyUtil.get_yyyy_MM(); //为空则获取本月的年月
            jsonObject.put("readYearMonth",meterMonth);
            query.like("billYearMonth",meterMonth);
        }

        String feeTypeNo = jsonObject.getString("feeTypeNo");
        if(!StringUtil.isEmpty(feeTypeNo)){
            query.eq("feeTypeNo",feeTypeNo);
        }
        String billDate = jsonObject.getString("billDate");
        if(!StringUtil.isEmpty(billDate)){
            query.eq("billDate",billDate);
        }

        query.eq("deleted", false);
        query.eq(jsonObject,"billStatus");
        query.eq(jsonObject,"houseNo");
        query.eq(jsonObject,"userNo");
        query.eq(jsonObject,"communityNo");
        query.eq(jsonObject,"buildNo");

        if(token.getUserType().equals(UserType.webwork)){
            JSONArray stewardHouseNos = commonClient.getStewardHouseNos(token.getUuid());
            query.in("houseNo",stewardHouseNos);

        }else {
            query.eq(jsonObject,"houseNo");
        }

        String search = jsonObject.getString("search");
        if(!Objects.isEmpty(search)){
            query.markBegin();
            query.like("communityName", search).or();
            query.like("chargeName", search).or();
            query.markEnd();
        }

        query.groupBy("houseNo");
        query.groupBy("userNo");
        query.page(jsonObject);

        // 根据以上条件查询房屋的分页数据
        Page<ChargeBillItemAppDTO> page = chargeBillItemDao.findHouseBillTotal(query);
        return page;
    }

    @Override
    public List<ChargeBillItemAppDTO> findBillUser(JSONObject jsonObject, Token token) {

        QueryCondition query = Condition.createQuery(false);

        query.eq("deleted", false);
        query.eq(jsonObject,"billStatus");
        query.eq(jsonObject,"userNo");
        query.eq(jsonObject,"houseNo");
        query.eq(jsonObject,"houseBillNo");
        query.noEq("billStatus",ChargeBillStatusEnum.BILL_PAID_CLOSE);

        //1、按周期或 2、按收费项统计
        if("2".equals(jsonObject.getString("type"))){
            query.eq(jsonObject,"feeTypeNo");
            query.groupBy("feeTypeNo");
        }else{
            query.eq(jsonObject,"billYearMonth");
            query.order("billYearMonth", "desc");
            query.groupBy("billYearMonth");
        }

        return chargeBillItemDao.findHouseBillUser(query);
    }

    @Override
    public List<ChargeBillItems> findBillDown(JSONObject jsonObject, Token token) {
        List<ChargeBillItems> itemsList = jdbcSession.findArray(ChargeBillItems.class)
                .eq("deleted", false)
                .eq(jsonObject,"userNo")
                .eq(jsonObject,"houseNo")
                .eq(jsonObject, "feeTypeNo")
                .noEq("billStatus", ChargeBillStatusEnum.BILL_PAID_CLOSE.getId())
                .eq(jsonObject,"houseBillNo")
                .like(jsonObject,"billYearMonth")
                .exe();
        return itemsList;
    }

    @Override
    public List<ChargeBillItemAppDTO> findBillAmount(JSONObject jsonObject, Token token) {

        //跨库请求数据
        PageCondition query = Condition.createPage(false);
        query.eq("deleted", false);
        query.in("billStatus", ChargeBillStatusEnum.BILL_WAIT_PAID.getId());
        query.eq(jsonObject,"houseNo");
        query.eq(jsonObject,"userNo");
        query.groupBy("houseNo");

        return chargeBillItemDao.findBillAmount(query);
    }


    @Override
    public void templateExport(HttpServletRequest req, HttpServletResponse resp) {
        excelTool.excelDownload(req, resp, template, "类目收费账单导入模板");
    }

    @Override
    public Object submitItems(JSONObject jsonObject, Token token) {

        return 0;
    }

    @Override
    public List<ChargeBillItems> findItems(JSONObject jsonObject, Token token) {

        return jdbcSession.findArray(ChargeBillItems.class)
                .eq("deleted", false)
                .in(jsonObject,"itemBillNo")
                .eq(jsonObject,"houseNo")
                .exe();
    }

    @Override
    public void importExcelCheck(JSONObject jsonObject) {
        excelTool.excelImport(jsonObject, BillItemsImportDTO.class);
    }


    @Override
    public Object importExcel(JSONObject jsonObject, Token token) throws Exception {
        //初始化excel数据开始的行数，为每个数据添加下标
        Integer[] arr = {3};

        List<BillItemsImportDTO> dtoList = excelTool.excelImport(jsonObject, BillItemsImportDTO.class);
        dtoList = dtoList.stream().peek(d->d.setIndex(arr[0]++)).collect(Collectors.toList());

        int importTotal = dtoList.size();
        StringBuilder errorMsg = new StringBuilder();

        //账单时间格式校验
        Set<String> importBillYearMonthSet = dtoList.stream().map(d -> d.getBillYearMonth().trim()).collect(Collectors.toSet());
        Set<String> unRegexYearMonthSet = new HashSet<>(importBillYearMonthSet);
        unRegexYearMonthSet.removeIf(m-> RegexConstant.YEAR_MONTH_REGEX.matcher(m).find());
        if(!Objects.isEmpty(unRegexYearMonthSet)){
            List<Integer> errIndex = dtoList.stream().filter(d -> unRegexYearMonthSet.contains(d.getBillYearMonth()))
                    .map(d -> d.getIndex()).collect(Collectors.toList());

            errorMsg.append(String.format("第%s行,[账单年月]格式错误请检查.\n", errIndex));
            ImportResult errResult = getImportResult(dtoList, importTotal, errorMsg, errIndex);
            if (errResult != null) {return errResult;}
        }
        //费用类型
        Set<String> importFeeTypeSet = dtoList.stream().map(d -> d.getFeeTypeName().trim()).collect(Collectors.toSet());
        List<ChargeTypeTemp> dbFeeTypeList = jdbcSession.findArray(ChargeTypeTemp.class)
                .eq("deleted", false)
                .eq("status", true)
                .in("feeTypeName", importFeeTypeSet)
                .exe();
        if(Objects.isEmpty(dbFeeTypeList)){
            errorMsg.append("导入的[账单类型]系统中均未找到.");
            return excelTool.R(importTotal,0,errorMsg.toString());
        }
        Set<String> dbFeeTypeNames = dbFeeTypeList.stream().map(d -> d.getFeeTypeName().trim()).collect(Collectors.toSet());
        List<Integer> notFeeTypes = dtoList.stream().filter(d -> !dbFeeTypeNames.contains(d.getFeeTypeName()))
                .map(d -> d.getIndex()).collect(Collectors.toList());

        if(!Objects.isEmpty(notFeeTypes)){
            errorMsg.append(String.format("第%s行,[账单类型]在系统中未找到.\n", notFeeTypes));
            ImportResult errResult = getImportResult(dtoList, importTotal, errorMsg, notFeeTypes);
            if (errResult != null) {return errResult;}
        }

        //账单金额校验
        Set<String> importAmountSet = dtoList.stream().map(d -> d.getBillAmount().trim()).collect(Collectors.toSet());
        Set<String> unRegexAmountSet = new HashSet<>(importAmountSet);
        unRegexAmountSet.removeIf(m->RegexConstant.MONEY_REGEX.matcher(m).find());
        if(!Objects.isEmpty(unRegexAmountSet)){
            List<Integer> errIndex = dtoList.stream().filter(d -> unRegexAmountSet.contains(d.getBillAmount()))
                    .map(d -> d.getIndex()).collect(Collectors.toList());

            errorMsg.append(String.format("第%s行,[账单金额]错误，金额只能是数字.\n", errIndex));
            ImportResult errResult = getImportResult(dtoList, importTotal, errorMsg, errIndex);
            if (errResult != null) {return errResult;}
        }

        List<Integer> errIndex = dtoList.stream().filter(d -> BigDecimalUtils.isLessThan(new BigDecimal(d.getBillAmount()), BigDecimal.ZERO))
                .map(d -> d.getIndex()).collect(Collectors.toList());
        if(!Objects.isEmpty(errIndex)){
            errorMsg.append(String.format("第%s行,[账单金额]错误，金额不能小于零.\n", errIndex));
            ImportResult errResult = getImportResult(dtoList, importTotal, errorMsg, errIndex);
            if (errResult != null) {return errResult;}
        }
        //记账时间校验
        Set<String> importCycleStartSet = dtoList.stream().map(d -> d.getBillCycleStart().trim()).collect(Collectors.toSet());
        Set<String> unRegexCycleStartSet = new HashSet<>(importCycleStartSet);
        unRegexCycleStartSet.removeIf(m-> RegexConstant.YEAR_MONTH_DAY_REGEX.matcher(m).find());
        if(!Objects.isEmpty(unRegexCycleStartSet)){
            List<Integer> errorIndex = dtoList.stream().filter(d -> unRegexCycleStartSet.contains(d.getBillCycleStart()))
                    .map(d -> d.getIndex()).collect(Collectors.toList());

            errorMsg.append(String.format("第%s行,[记账开始时间]格式错误请检查.\n", errorIndex));
            ImportResult errResult = getImportResult(dtoList, importTotal, errorMsg, errorIndex);
            if (errResult != null) {return errResult;}
        }
        Set<String> importCycleEndSet = dtoList.stream().map(d -> d.getBillCycleEnd().trim()).collect(Collectors.toSet());
        Set<String> unRegexCycleEndSet = new HashSet<>(importCycleEndSet);
        unRegexCycleEndSet.removeIf(m-> RegexConstant.YEAR_MONTH_DAY_REGEX.matcher(m).find());
        if(!Objects.isEmpty(unRegexCycleEndSet)){
            List<Integer> errorIndex = dtoList.stream().filter(d -> unRegexCycleEndSet.contains(d.getBillCycleEnd()))
                    .map(d -> d.getIndex()).collect(Collectors.toList());

            errorMsg.append(String.format("第%s行,[记账截止时间]格式错误请检查.\n", errorIndex));
            ImportResult errResult = getImportResult(dtoList, importTotal, errorMsg, errorIndex);
            if (errResult != null) {return errResult;}
        }

        List<Integer> cycleErrorIndex = dtoList.stream().filter(d -> DailyUtil.intervalDays(d.getBillCycleStart(), d.getBillCycleEnd()) < 0)
                .map(d -> d.getIndex()).collect(Collectors.toList());

        if(!Objects.isEmpty(cycleErrorIndex)){
            errorMsg.append(String.format("第%s行,[记账开始时间]必须小于[记账截止时间].\n", cycleErrorIndex));
            ImportResult errResult = getImportResult(dtoList, importTotal, errorMsg, cycleErrorIndex);
            if (errResult != null) {return errResult;}
        }
        //出账时间校验
        Set<String> importBillDateSet = dtoList.stream().map(d -> d.getBillDate().trim()).collect(Collectors.toSet());
        Set<String> unRegexBillDateSet = new HashSet<>(importBillDateSet);
        unRegexBillDateSet.removeIf(m-> RegexConstant.YEAR_MONTH_DAY_REGEX.matcher(m).find());
        if(!Objects.isEmpty(unRegexBillDateSet)){
            List<Integer> errorIndex = dtoList.stream().filter(d -> unRegexBillDateSet.contains(d.getBillDate()))
                    .map(d -> d.getIndex()).collect(Collectors.toList());

            errorMsg.append(String.format("第%s行,[出账时间]格式错误请检查.\n", errorIndex));
            ImportResult errResult = getImportResult(dtoList, importTotal, errorMsg, errorIndex);
            if (errResult != null) {return errResult;}
        }
        List<Integer> billDataErrorIndex = dtoList.stream().filter(d -> DailyUtil.intervalDays(d.getBillCycleEnd(), d.getBillDate()) < 0)
                .map(d -> d.getIndex()).collect(Collectors.toList());
        if(!Objects.isEmpty(billDataErrorIndex)){
            errorMsg.append(String.format("第%s行,[出账时间]不能小于[记账时间].\n", billDataErrorIndex));
            ImportResult errResult = getImportResult(dtoList, importTotal, errorMsg, billDataErrorIndex);
            if (errResult != null) {return errResult;}
        }
        //缴费时间
        Set<String> importChargeDateSet = dtoList.stream().map(d -> d.getChargeDate().trim()).collect(Collectors.toSet());
        Set<String> unRegexChargeDateSet = new HashSet<>(importChargeDateSet);
        unRegexChargeDateSet.removeIf(m-> RegexConstant.YEAR_MONTH_DAY_REGEX.matcher(m).find());
        if(!Objects.isEmpty(unRegexChargeDateSet)){
            List<Integer> errorIndex = dtoList.stream().filter(d -> unRegexChargeDateSet.contains(d.getChargeDate()))
                    .map(d -> d.getIndex()).collect(Collectors.toList());

            errorMsg.append(String.format("第%s行,[缴费开始时间]格式错误请检查.\n", errorIndex));
            ImportResult errResult = getImportResult(dtoList, importTotal, errorMsg, errorIndex);
            if (errResult != null) {return errResult;}
        }

        List<Integer> chargeDateErrorIndex = dtoList.stream().filter(d -> DailyUtil.intervalDays(d.getBillDate(), d.getChargeDate()) < 0)
                .map(d -> d.getIndex()).collect(Collectors.toList());
        if(!Objects.isEmpty(chargeDateErrorIndex)){
            errorMsg.append(String.format("第%s行,[缴费开始时间]不能小于[出账时间].\n", chargeDateErrorIndex));
            ImportResult errResult = getImportResult(dtoList, importTotal, errorMsg, chargeDateErrorIndex);
            if (errResult != null) {return errResult;}
        }
        //违约时间
        Set<String> importPenaltyDateSet = dtoList.stream().map(d -> d.getPenaltyDate().trim()).collect(Collectors.toSet());
        Set<String> unRegexPenaltyDateSet = new HashSet<>(importPenaltyDateSet);
        unRegexPenaltyDateSet.removeIf(m-> RegexConstant.YEAR_MONTH_DAY_REGEX.matcher(m).find());
        if(!Objects.isEmpty(unRegexPenaltyDateSet)){
            List<Integer> errorIndex = dtoList.stream().filter(d -> unRegexPenaltyDateSet.contains(d.getPenaltyDate()))
                    .map(d -> d.getIndex()).collect(Collectors.toList());

            errorMsg.append(String.format("第%s行,[违约时间]格式错误请检查.\n", errorIndex));
            ImportResult errResult = getImportResult(dtoList, importTotal, errorMsg, errorIndex);
            if (errResult != null) {return errResult;}
        }

        List<Integer> penaltyDateErrorIndex = dtoList.stream().filter(d -> DailyUtil.intervalDays(d.getChargeDate(), d.getPenaltyDate()) < 0)
                .map(d -> d.getIndex()).collect(Collectors.toList());
        if(!Objects.isEmpty(penaltyDateErrorIndex)){
            errorMsg.append(String.format("第%s行,[违约时间]不能小于[缴费开始时间].\n", penaltyDateErrorIndex));
            ImportResult errResult = getImportResult(dtoList, importTotal, errorMsg, penaltyDateErrorIndex);
            if (errResult != null) {return errResult;}
        }

        //缴费人手机号码格式校验
        Set<String> importMobileSet = dtoList.stream().map(d -> d.getMobile().trim()).collect(Collectors.toSet());
        Set<String> unRegexMobileSet = new HashSet<>(importMobileSet);
        unRegexMobileSet.removeIf(m->RegexConstant.MOBILE_REGEX.matcher(m).find());
        if(!Objects.isEmpty(unRegexMobileSet)){
            List<Integer> errorIndex = dtoList.stream().filter(d -> unRegexMobileSet.contains(d.getMobile()))
                    .map(d -> d.getIndex()).collect(Collectors.toList());

            errorMsg.append(String.format("第%s行,[缴费人手机号码]格式错误请检查.\n", errorIndex));
            ImportResult errResult = getImportResult(dtoList, importTotal, errorMsg, errorIndex);
            if (errResult != null) {return errResult;}
        }
        //住户信息校验
        Set<String> importBuildSet = dtoList.stream().map(d -> d.getBuildName().trim()).collect(Collectors.toSet());
        Set<String> importUnitNameSet = dtoList.stream().map(d -> d.getUnitName()).collect(Collectors.toSet());
        importUnitNameSet.removeAll(Collections.singleton(null));
        Set<String> importCommunitySet = dtoList.stream().map(d -> d.getCommunityName().trim()).collect(Collectors.toSet());
        Set<String> houseNumberSet = dtoList.stream().map(d -> d.getHouseNumber().trim()).collect(Collectors.toSet());
        Set<String> importChargeNameSet = dtoList.stream().map(d -> d.getChargeName().trim()).collect(Collectors.toSet());

        JSONObject json = new JSONObject();
        json.put("communitySet",importCommunitySet);
        json.put("buildSet",importBuildSet);
        json.put("unitNameSet",importUnitNameSet);
        json.put("houseNumberSet",houseNumberSet);
        json.put("chargeNameSet",importChargeNameSet);
        List<ChargeHouseConfig> dbHouseList = commonClient.getResidentListByMultipleQuery(json);
        if(Objects.isEmpty(dbHouseList)){
            errorMsg.append("导入的[小区名称]、[楼栋]、[单元](非必填)、[房号]无法匹配到住户信息.");
            return excelTool.R(importTotal,0,errorMsg.toString());
        }
        dtoList.forEach(d->{
            //格式统一化
            String buildName = d.getBuildName();
            if(!Objects.isEmpty(buildName) && !buildName.contains("栋")){
                d.setBuildName(buildName+"栋");
            }
            String unitName = d.getUnitName();
            if(!Objects.isEmpty(unitName) && !unitName.contains("单元")){
                d.setUnitName(unitName+"单元");
            }
        });

        //装填houseNo
        dtoList.stream().map(d->dbHouseList.stream().filter(h->h.getCommunityName().equals(d.getCommunityName())
                && h.getBuildName().equals(d.getBuildName()) && h.getMergeName().equals(d.getHouseNumber()) &&
                h.getChargeName().equals(d.getChargeName())).findFirst().map(h->{
                    d.setHouseNo(h.getHouseNo());
                    d.setCommunityNo(h.getCommunityNo());
                    d.setUserNo(h.getOwnerNo());
                    d.setResidentsType(h.getReType());
                    return d;
        }).orElse(null)).filter(java.util.Objects::nonNull).collect(Collectors.toList());
        //无法匹配的房屋
        List<Integer> errorHouseList = dtoList.stream().filter(d -> StringUtil.isEmpty(d.getHouseNo())).map(d ->d.getIndex()).collect(Collectors.toList());
        if(!Objects.isEmpty(errorHouseList)){
            errorMsg.append(String.format("第%s行,[小区名称]、[楼栋]、[单元](非必填)、[房号]无法匹配到住户信息.\n", errorHouseList));
            ImportResult errResult = getImportResult(dtoList, importTotal, errorMsg, errorHouseList);
            if (errResult != null) {return errResult;}
        }

        //判断t_charge_bill_house表是否已经存在本房间本月的账单数据
        Set<String> dbHouseNos = dbHouseList.stream().map(h -> h.getHouseNo()).collect(Collectors.toSet());
        FindArray<ChargeBillHouse> houseBillFind = jdbcSession.findArray(ChargeBillHouse.class);
        houseBillFind.eq("deleted",false);
        houseBillFind.in("houseNo",dbHouseNos);
        houseBillFind.in("billYearMonth",importBillYearMonthSet);
        List<ChargeBillHouse> dbBillHouses = houseBillFind.exe();

        List<BillItemsImportDTO> finalDtoList = dtoList;
        List<Integer> errorStateList = dbBillHouses.stream().map(h-> finalDtoList.stream().filter(i->
                i.getHouseNo().equals(h.getHouseNo()) && i.getBillYearMonth().equals(h.getBillYearMonth())
                        && !h.getBillStatus().equals(ChargeBillStatusEnum.BILL_WAIT_PAID)
                        && !h.getBillStatus().equals(ChargeBillStatusEnum.BILL_PAID_CLOSE)
        ).findFirst().map(i->i.getIndex()).orElse(null)).filter(java.util.Objects::nonNull).collect(Collectors.toList());
        if(!Objects.isEmpty(errorStateList)){
            errorMsg.append(String.format("第%s行,房屋该月已经产生支付订单，无法导入,请移步网页处理.\n", errorStateList));
            ImportResult errResult = getImportResult(dtoList, importTotal, errorMsg, errorStateList);
            if (errResult != null) {return errResult;}
        }

        //操作人信息
        String departmentNo = null;
        String departmentName = null;
        List<String> departList = token.getDepartments();
        List<String> departmentNos = null;
        String username = token.getUsername();
        if (!Objects.isEmpty(departList)) {
            DeptDTO dept = deptService.getDept(departList.get(0));
            departmentNo = dept.getDepartmentNo();
            departmentName = dept.getDepartmentName();
            departmentNos = dept.getDepartmentNos();
        }
        //orm
        List<ChargeBillItems> orm = new ArrayList<>();
        for(Iterator<BillItemsImportDTO> i = dtoList.iterator(); i.hasNext(); ) {
            BillItemsImportDTO importDTO = i.next();


            List<Integer> index = new ArrayList<>();
            index.add(importDTO.getIndex());

            ChargeBillItems chargeBillItems = Pool.newInstance(ChargeBillItems.class);
            Beans.from((JSONObject) JSONObject.toJSON(importDTO)).to(chargeBillItems).igornNull().copy();

            String address = ConvertUtil.jointAddress(importDTO.getCommunityName()
                    , importDTO.getBuildName(), importDTO.getUnitName(), importDTO.getHouseNumber());

            ChargeTypeTemp chargeTypeTemp = dbFeeTypeList.stream().filter(db -> db.getFeeTypeName()
                    .equals(importDTO.getFeeTypeName())).findFirst().orElse(null);
            if(null != chargeTypeTemp){
                chargeBillItems.setFeeTypeNo(chargeTypeTemp.getId());
            }
            
            //如果该月已有房屋订单，需要填充房屋和小区账单编号
            ChargeBillHouse oldHouseBill = dbBillHouses.stream().filter(dbHouse -> importDTO.getHouseNo().equals(dbHouse.getHouseNo())
                    && importDTO.getBillYearMonth().equals(dbHouse.getBillYearMonth())).findFirst().orElse(null);
            if(null != oldHouseBill){
                chargeBillItems.setHouseBillNo(oldHouseBill.getHouseBillNo());
                chargeBillItems.setCommunityBillNo(oldHouseBill.getCommunityBillNo());
            }

            chargeBillItems.setResidentsType(importDTO.getResidentsType());
            chargeBillItems.setBillAddress(address);
            chargeBillItems.setItemBillNo(CommonUtil.generateRandom("FX"));
            chargeBillItems.setBillStatus(ChargeBillStatusEnum.BILL_WAIT_PAID);
            chargeBillItems.setPayableAmount(new BigDecimal(importDTO.getBillAmount()));
            chargeBillItems.setRemark("批量导入订单");
            chargeBillItems.setCreateName(username);
            chargeBillItems.setDepartmentNo(departmentNo);
            chargeBillItems.setDepartmentNos(departmentNos);
            chargeBillItems.setDepartmentName(departmentName);

            orm.add(chargeBillItems);
        }
        //对导入的类目账单按房屋和年月分组-----------------------------------------------------------------------------------------------
        List<ChargeBillItems> groupBillList = orm.stream().collect(
                Collectors.toMap(h->h.getHouseNo().trim()+h.getBillYearMonth().trim(),h->h,(o1,o2)->{
                    BigDecimal newBillAmount = BigDecimalUtils.add(o1.getBillAmount(), o2.getBillAmount());
                    o1.setBillAmount(newBillAmount);
                    return o1;
                })).values().stream().collect(Collectors.toList());
        //历史房屋账单金额处理---------------------------------------------------------------------------------------------------
        List<BillItemsImportDTO> finalDtoList1 = dtoList;
        dbBillHouses.stream().map(h-> finalDtoList1.stream().filter(i->i.getHouseNo().equals(h.getHouseNo())
                && i.getBillYearMonth().equals(h.getBillYearMonth())).findFirst().map(i->{
            BigDecimal newBillAmount = BigDecimalUtils.add(h.getBillAmount(), new BigDecimal(i.getBillAmount()));
            h.setBillAmount(newBillAmount);
            h.setBillAmountUnPaid(newBillAmount);
            //此处需要填充id所以无法使用分组数据
            i.setHouseBillNo(h.getHouseBillNo());
            return h;
        }).orElse(null)).filter(java.util.Objects::nonNull).collect(Collectors.toList());
        jdbcSession.updateById(dbBillHouses);
        //找出未匹配到的房屋历史订单
        Map<String,ChargeBillItems> notHouseBillMap = new HashMap<>();
        Map<String,ChargeBillItems> notCommunityBillMap = new HashMap<>();
        for (ChargeBillItems groupItem : groupBillList) {
            notHouseBillMap.put(groupItem.getHouseNo()+groupItem.getBillYearMonth(),groupItem);
            notCommunityBillMap.put(groupItem.getCommunityNo()+groupItem.getBillYearMonth(),groupItem);
        }
        for (ChargeBillHouse dbBillHouse : dbBillHouses) {
            String key = dbBillHouse.getHouseNo()+ dbBillHouse.getBillYearMonth();
            if(notHouseBillMap.get(key) != null){
                notHouseBillMap.remove(key);
            }
        }
        List<ChargeBillItems> notHouseBillList = new ArrayList<>(notHouseBillMap.values());

        List<ChargeBillHouse> newHouseBillList = new ArrayList();
        for (ChargeBillItems notHouseBill : notHouseBillList) {
            ChargeBillHouse houseBill = Pool.newInstance(ChargeBillHouse.class);
            Beans.from((JSONObject) JSONObject.toJSON(notHouseBill)).to(houseBill).include("id").igornNull().copy();
            houseBill.setBillStatus(notHouseBill.getBillStatus());
            houseBill.setResidentsType(notHouseBill.getResidentsType());
            houseBill.setBillAmountUnPaid(houseBill.getBillAmount());
            houseBill.setHouseBillNo(CommonUtil.generateRandom("FW"));
            houseBill.setOperaDesc("批量导入订单");
            newHouseBillList.add(houseBill);
        }


        //小区账单处理---------------------------------------------------------------------------------------------------
        //判断t_charge_bill_community表是否存在本小区的本月的账单数据
        Set<String> dbCommunityNos = dbHouseList.stream().map(h -> h.getCommunityNo().trim()).collect(Collectors.toSet());
        FindArray<ChargeBillCommunity> communityBillFind = jdbcSession.findArray(ChargeBillCommunity.class);
        communityBillFind.eq("deleted",false);
        communityBillFind.in("communityNo",dbCommunityNos);
        communityBillFind.in("billYearMonth",importBillYearMonthSet);
        List<ChargeBillCommunity> dbCommunityBills = communityBillFind.exe();
        //合并更新已有的小区订单
        dbCommunityBills.stream().map(c->finalDtoList1.stream().filter(i->i.getCommunityNo().equals(c.getCommunityNo())
                && i.getBillYearMonth().equals(c.getBillYearMonth())).findFirst().map(i->{
            BigDecimal newBillAmount = BigDecimalUtils.add(c.getBillAmountTotal(), new BigDecimal(i.getBillAmount()));
            c.setBillAmountTotal(newBillAmount);
            c.setBillTotal(c.getBillTotal()+1);
            return c;
        }).orElse(null)).filter(java.util.Objects::nonNull).collect(Collectors.toList());
        jdbcSession.updateById(dbCommunityBills);
        //找出未匹配到的小区历史账单
        for (ChargeBillCommunity dbBillCommunity : dbCommunityBills) {
            String key = dbBillCommunity.getCommunityNo()+ dbBillCommunity.getBillYearMonth();
            if(notCommunityBillMap.get(key) != null){
                notCommunityBillMap.remove(key);
            }
        }
        List<ChargeBillItems> notCommunityBills = new ArrayList<>(notCommunityBillMap.values());

        List<ChargeBillCommunity> newCommunityBillList = new ArrayList<>();
        for (ChargeBillItems notCommunityBill : notCommunityBills) {
            ChargeBillCommunity billCommunity = Pool.newInstance(ChargeBillCommunity.class);
            Beans.from((JSONObject) JSONObject.toJSON(notCommunityBill)).to(billCommunity).include("id").igornNull().copy();
            billCommunity.setBillStatus(notCommunityBill.getBillStatus());
            billCommunity.setCommunityBillNo(CommonUtil.generateRandom("XQ"));
            billCommunity.setBillAmountTotal(notCommunityBill.getBillAmount());
            billCommunity.setOperaDesc("批量导入订单");
            newCommunityBillList.add(billCommunity);
        }
        //回填类目账单数量
        newCommunityBillList.stream().map(c->finalDtoList1.stream().filter(d->d.getCommunityNo().equals(c.getCommunityNo())
                && d.getBillYearMonth().equals(c.getBillYearMonth())).findFirst().map(d->{
                    c.setBillTotal(c.getBillTotal()+1);
                    return c;
        }).orElse(null)).filter(java.util.Objects::nonNull).collect(Collectors.toList());

        jdbcSession.insert(newCommunityBillList);

        //回填房屋账单的小区账单ID关联字段
        newHouseBillList.stream().map(h-> newCommunityBillList.stream().filter(c->c.getCommunityNo().equals(h.getCommunityNo())
                && c.getBillYearMonth().equals(h.getBillYearMonth())).findFirst().map(o->{
            h.setCommunityBillNo(o.getCommunityBillNo());
            return h;
        }).orElse(null)).filter(java.util.Objects::nonNull).collect(Collectors.toList());
        jdbcSession.insert(newHouseBillList);

        //填充新增的房屋订单ID、小区账单ID关联字段到类目账单中
        orm.stream().map(o->newHouseBillList.stream().filter(h->h.getHouseNo().equals(o.getHouseNo())
                && h.getBillYearMonth().equals(o.getBillYearMonth())).findFirst().map(h->{
            o.setHouseBillNo(h.getHouseBillNo());
            return o;
        }).orElse(null)).filter(java.util.Objects::nonNull).collect(Collectors.toList());

        orm.stream().map(o-> newCommunityBillList.stream().filter(c->c.getCommunityNo().equals(o.getCommunityNo())
                && c.getBillYearMonth().equals(o.getBillYearMonth())).findFirst().map(c->{
            o.setCommunityBillNo(c.getCommunityBillNo());
            return o;
        }).orElse(null)).filter(java.util.Objects::nonNull).collect(Collectors.toList());


        int successCount = jdbcSession.insert(orm).length;
        return excelTool.R(importTotal,successCount,errorMsg.toString());
    }

    private ImportResult getImportResult(List<BillItemsImportDTO> dtoList, int importTotal, StringBuilder errorMsg, List<Integer> errorIndex) {
        dtoList.removeIf(d->errorIndex.contains(d.getIndex()));
        if(Objects.isEmpty(dtoList)){
            return excelTool.R(importTotal,0, errorMsg.toString());
        }
        return null;
    }


    /**
     * 发送消息
     * @param manage 模板替换内容对象
     * @param uuid 用户ID
     * @param templateType  模板CODE
     * @param phoneList 手机号
     * @param userCreate 是否小程序用户，false 员工，true 小程序用户
     * @throws Exception
     */
    private void msgUniformSend(ChargeBillItems manage, String uuid, TemplateType templateType, List<String> phoneList, boolean userCreate, String companyNo) throws Exception {
        // 获取消息推送配置文件
        JSONObject configJson = doveClient.post("/crm/message/global/config/service/get", (http) -> {
            http.addParam("templateType", templateType.getId());
        });

        if (configJson.getIntValue("code") == 200) {
            UniformSendReqDto dto = getUniformSendReqDto(manage, uuid, templateType, phoneList, configJson.getJSONObject("data"), userCreate, companyNo);
            // 消息推送
            doveClient.post("/crm/message/service/uniformSend", (http) -> {
                http.addParam("temp", JSONObject.toJSONString(dto));
            });
        }
    }

    private UniformSendReqDto getUniformSendReqDto(ChargeBillItems manage, String uuid, TemplateType templateType,
                                                   List<String> phoneList, JSONObject dataJson, boolean userCreate,
                                                   String companyNo) throws Exception {
        UniformSendReqDto dto = new UniformSendReqDto();
        dto.setMsgId(manage.getId());
        dto.setBusinessTypeId(MessageBusinessEnum.MSG_BILL_PAY.getId());
        dto.setTemplateTypeId(templateType.getId());
        dto.setCompanyNo(companyNo);
        dto.setUserCreate(userCreate);
        dto.setUuid(uuid);
        dto.setTitle("缴费提醒");

        JSONObject inner = dataJson.getJSONObject("messageTemplateInner");
        if (dataJson.getBooleanValue("innerSend") && org.coraframework.util.Objects.nonNull(inner)) {
            dto.setTitle(inner.getString("title"));
            dto.setInnerData(TemplateReplaceUtil.replace(inner.getString("content"), inner.getString("tagArray"), manage));
            dto.setPageParam(String.format("companyNo=%s&houseNo=%s", companyNo, manage.getHouseNo()));
        }

        JSONObject sms = dataJson.getJSONObject("messageTemplateSms");
        if (dataJson.getBooleanValue("smsSend") && org.coraframework.util.Objects.nonNull(sms)) {
            if (org.coraframework.util.Objects.nonEmpty(phoneList)){
                dto.setPhoneList(phoneList);
                dto.setSmsDataParams(TemplateReplaceUtil.listTencentSmsParam(sms.getString("content"), sms.getString("tagArray"), manage));
            } else {
                JSONObject jsonObject = doveClient.post("/kht-bin/auth/user/listPhone", http -> {
                    JSONObject temp = new JSONObject();
                    temp.put("id", Arrays.asList(uuid));
                    http.setBody(temp.toJSONString());
                });
                List<String> listPhone = JSONArray.parseArray(jsonObject.getString("data"), String.class);
                if (org.coraframework.util.Objects.nonEmpty(listPhone)){
                    dto.setPhoneList(listPhone);
                    dto.setSmsDataParams(TemplateReplaceUtil.listTencentSmsParam(sms.getString("content"), sms.getString("tagArray"), manage));
                }
            }

        }

        String wxchatTemplate = dataJson.getString("wxchatTemplate");
        if (dataJson.getBooleanValue("wxchatSend") && org.coraframework.util.Objects.nonNull(wxchatTemplate)) {
            dto.setWxChatData(getWxchatTemplate(templateType, manage));
            dto.setPageParam(String.format("companyNo=%s&houseNo=%s", companyNo, manage.getHouseNo()));
        }

        return dto;
    }

    private String getWxchatTemplate(TemplateType templateType, ChargeBillItems manage){
        JSONObject data = new JSONObject();

        if(TemplateType.PAY_C_MONTH_BILL_DATE == templateType){

            data.put("first", buildMpMessageData("您的月度账单已生成"));
            // 房号
            data.put("keyword1", buildMpMessageData(manage.getBillAddress()));
            // 账单年月
            data.put("keyword2", buildMpMessageData(manage.getBillYearMonth()));
            // 账单金额
            data.put("keyword3", buildMpMessageData(BigDecimalUtils.format(manage.getBillAmount()).toPlainString()));

            data.put("remark", buildMpMessageData("感谢您的关注和支持！如有疑问，请咨询物业管理处"));
            return data.toJSONString();
        } else {
            return null;
        }
    }

    private JSONObject buildMpMessageData(String value) {
        JSONObject json = new JSONObject();
        json.put("value", value);
        return json;
    }
}
